"""
数据源管理API接口
"""
from fastapi import APIRouter, Depends, HTTPException, UploadFile, File, Form
from sqlalchemy.orm import Session
from pydantic import BaseModel, Field
from typing import Dict, Any, List, Optional, Union
import json
import tempfile
import os
from pathlib import Path
from datetime import datetime

from core.database import get_db
from core.logging_config import get_logger, log_api_request
from core.exceptions import DatabaseError, ValidationError
from core.auth import get_current_user, require_permissions
from models.user import User
from models.data_source import DataSource
from services.data_source_manager import data_source_manager
from services.data_source_connection import get_supported_database_types
from services.excel_data_processor import excel_processor, excel_template

# 路由器
data_sources_router = APIRouter(tags=["数据源管理"])

# 日志记录器
logger = get_logger("data_sources_api")


class DataSourceCreate(BaseModel):
    """创建数据源请求模型"""
    name: str = Field(..., min_length=1, max_length=100, description="数据源名称")
    description: str = Field("", max_length=500, description="数据源描述")
    type: str = Field(..., description="数据源类型 (database, excel)")
    config: Dict[str, Any] = Field(..., description="数据源配置")


class DataSourceUpdate(BaseModel):
    """更新数据源请求模型"""
    name: Optional[str] = Field(None, min_length=1, max_length=100, description="数据源名称")
    description: Optional[str] = Field(None, max_length=500, description="数据源描述")
    config: Optional[Dict[str, Any]] = Field(None, description="数据源配置")


class DataSourceResponse(BaseModel):
    """数据源响应模型"""
    id: int
    name: str
    description: str
    type: str
    status: str
    config: Optional[Dict[str, Any]]
    creator_id: int
    created_at: str
    updated_at: str
    last_connected: Optional[str]
    connection_info: Optional[Dict[str, Any]]
    preview_enabled: bool
    preview_limit: int
    category: Optional[str]
    tags: Optional[List[str]]
    priority: int


class ConnectionTestRequest(BaseModel):
    """连接测试请求模型"""
    type: str = Field(..., description="数据源类型")
    config: Dict[str, Any] = Field(..., description="连接配置")


class DatabaseQueryRequest(BaseModel):
    """数据库查询请求模型"""
    data_source_id: int = Field(..., description="数据源ID")
    query: str = Field(..., min_length=1, description="SQL查询语句")
    limit: int = Field(1000, ge=1, le=10000, description="返回行数限制")


class ExcelUploadRequest(BaseModel):
    """Excel上传请求模型"""
    name: str = Field(..., min_length=1, max_length=100, description="数据源名称")
    description: str = Field("", max_length=500, description="数据源描述")
    sheet_name: Optional[str] = Field(None, description="工作表名称")
    has_header: bool = Field(True, description="是否有标题行")


class DataPreviewRequest(BaseModel):
    """数据预览请求模型"""
    table_name: Optional[str] = Field(None, description="表名（数据库类型）")
    sheet_name: Optional[str] = Field(None, description="工作表名（Excel类型）")
    limit: int = Field(100, ge=1, le=1000, description="预览行数限制")
    offset: int = Field(0, ge=0, description="偏移量")


class TableSchemaResponse(BaseModel):
    """表结构响应模型"""
    table_name: str
    columns: List[Dict[str, Any]]
    record_count: int
    column_count: int


class DataPreviewResponse(BaseModel):
    """数据预览响应模型"""
    table_name: str
    columns: List[Dict[str, Any]]
    data: List[Dict[str, Any]]
    total_records: int
    returned_records: int
    offset: int
    limit: int
    execution_time: int


@data_sources_router.post("/", response_model=DataSourceResponse)
@require_permissions(["data_source:create"])
@log_api_request("create_data_source")
async def create_data_source(
    data_source: DataSourceCreate,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """创建数据源"""
    logger.info(f"创建数据源请求: {data_source.name}")
    
    try:
        # 创建数据源
        config_dict = data_source.dict()
        new_data_source = data_source_manager.create_data_source(db, current_user.id, config_dict)
        
        return DataSourceResponse(
            id=new_data_source.id,
            name=new_data_source.name,
            description=new_data_source.description,
            type=new_data_source.source_type,
            status=new_data_source.status,
            config=new_data_source.config or {},
            creator_id=new_data_source.creator_id,
            created_at=new_data_source.created_at.isoformat(),
            updated_at=new_data_source.updated_at.isoformat(),
            last_connected=new_data_source.last_connected.isoformat() if new_data_source.last_connected else None,
            connection_info=new_data_source.connection_info,
            preview_enabled=new_data_source.preview_enabled,
            preview_limit=new_data_source.preview_limit,
            category=new_data_source.category,
            tags=new_data_source.tags,
            priority=new_data_source.priority
        )
        
    except Exception as e:
        logger.error(f"创建数据源失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/", response_model=List[DataSourceResponse])
@require_permissions(["data_source:read"])
@log_api_request("get_data_sources")
async def get_data_sources(
    type: Optional[str] = None,
    status: Optional[str] = None,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取数据源列表"""
    logger.info("获取数据源列表请求")
    
    try:
        data_sources = data_source_manager.get_data_sources(
            db, 
            user_id=current_user.id if not current_user.is_superuser else None,
            source_type=type,
            status=status
        )
        
        return [
            DataSourceResponse(
                id=ds.id,
                name=ds.name,
                description=ds.description,
                type=ds.source_type,
                status=ds.status,
                config=ds.config or {},
                creator_id=ds.creator_id,
                created_at=ds.created_at.isoformat(),
                updated_at=ds.updated_at.isoformat(),
                last_connected=ds.last_connected.isoformat() if ds.last_connected else None,
                connection_info=ds.connection_info,
                preview_enabled=ds.preview_enabled,
                preview_limit=ds.preview_limit,
                category=ds.category,
                tags=ds.tags,
                priority=ds.priority
            ) for ds in data_sources
        ]
        
    except Exception as e:
        logger.error(f"获取数据源列表失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/statistics")
@require_permissions(["data_source:read"])
@log_api_request("get_data_sources_statistics")
async def get_data_sources_statistics(
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取数据源统计信息"""
    logger.info("获取数据源统计信息请求")
    
    try:
        statistics = data_source_manager.get_data_sources_statistics(
            db, 
            user_id=current_user.id if not current_user.is_superuser else None
        )
        
        return {
            "code": 200,
            "message": "success",
            "data": statistics
        }
        
    except Exception as e:
        logger.error(f"获取数据源统计信息失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/{data_source_id}")
# @require_permissions(["data_source:read"])  # 临时注释掉权限检查进行测试
@log_api_request("get_data_source")
async def get_data_source(
    data_source_id: int,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取数据源详情"""
    logger.info(f"获取数据源详情请求: {data_source_id}")
    
    try:
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 暂时简化权限检查以进行测试
        # if not current_user.is_superuser and data_source.creator_id != current_user.id:
        #     raise HTTPException(status_code=403, detail="无权限访问此数据源")
        
        response_data = DataSourceResponse(
            id=data_source.id,
            name=data_source.name,
            description=data_source.description,
            type=data_source.source_type,
            status=data_source.status,
            config=data_source.config or {},
            creator_id=data_source.creator_id,
            created_at=data_source.created_at.isoformat(),
            updated_at=data_source.updated_at.isoformat(),
            last_connected=data_source.last_connected.isoformat() if data_source.last_connected else None,
            connection_info=data_source.connection_info,
            preview_enabled=data_source.preview_enabled,
            preview_limit=data_source.preview_limit,
            category=data_source.category,
            tags=data_source.tags,
            priority=data_source.priority
        )
        
        # 返回前端期望的格式
        return {
            "code": 200,
            "message": "success",
            "data": response_data.dict(),
            "timestamp": datetime.now().isoformat()
        }
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取数据源详情失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.put("/{data_source_id}", response_model=DataSourceResponse)
@require_permissions(["data_source:update"])
@log_api_request("update_data_source")
async def update_data_source(
    data_source_id: int,
    data_source: DataSourceUpdate,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """更新数据源"""
    logger.info(f"更新数据源请求: {data_source_id}")
    
    try:
        # 检查数据源是否存在
        existing_data_source = data_source_manager.get_data_source(db, data_source_id)
        if not existing_data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and existing_data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限修改此数据源")
        
        # 更新数据源
        update_dict = {k: v for k, v in data_source.dict().items() if v is not None}
        updated_data_source = data_source_manager.update_data_source(db, data_source_id, update_dict)
        
        return DataSourceResponse(
            id=updated_data_source.id,
            name=updated_data_source.name,
            description=updated_data_source.description,
            type=updated_data_source.source_type,
            status=updated_data_source.status,
            config=updated_data_source.config or {},
            creator_id=updated_data_source.creator_id,
            created_at=updated_data_source.created_at.isoformat(),
            updated_at=updated_data_source.updated_at.isoformat(),
            last_connected=updated_data_source.last_connected.isoformat() if updated_data_source.last_connected else None,
            connection_info=updated_data_source.connection_info,
            preview_enabled=updated_data_source.preview_enabled,
            preview_limit=updated_data_source.preview_limit,
            category=updated_data_source.category,
            tags=updated_data_source.tags,
            priority=updated_data_source.priority
        )
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"更新数据源失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.delete("/{data_source_id}")
@require_permissions(["data_source:delete"])
@log_api_request("delete_data_source")
async def delete_data_source(
    data_source_id: int,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """删除数据源"""
    logger.info(f"删除数据源请求: {data_source_id}")
    
    try:
        # 检查数据源是否存在
        existing_data_source = data_source_manager.get_data_source(db, data_source_id)
        if not existing_data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and existing_data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限删除此数据源")
        
        # 删除数据源
        success = data_source_manager.delete_data_source(db, data_source_id)
        
        return {
            "code": 200,
            "message": "数据源删除成功",
            "data": {"success": True},
            "timestamp": datetime.now().isoformat()
        }
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"删除数据源失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.post("/test-connection")
@require_permissions(["data_source:test"])
@log_api_request("test_connection")
async def test_connection(
    test_request: ConnectionTestRequest,
    current_user: User = Depends(get_current_user)
):
    """测试数据源连接"""
    logger.info("测试数据源连接请求")
    
    try:
        if test_request.type == "database":
            # 测试数据库连接
            from services.data_source_connection import DataSourceConnection
            connection = DataSourceConnection(test_request.config)
            result = connection.test_connection()
            connection.close()
            
            # 转换为标准API响应格式
            if result.get("success"):
                return {
                    "code": 200,
                    "message": result.get("message", "连接测试成功"),
                    "data": {
                        "connection_time": result.get("connection_time"),
                        "database_info": result.get("database_info")
                    },
                    "timestamp": datetime.now().isoformat()
                }
            else:
                return {
                    "code": 400,
                    "message": result.get("message", "连接测试失败"),
                    "data": None,
                    "timestamp": datetime.now().isoformat()
                }
        
        elif test_request.type == "excel":
            # 测试Excel文件
            file_path = test_request.config.get("file_path")
            if not file_path:
                return {
                    "code": 400,
                    "message": "Excel文件路径不能为空",
                    "data": None,
                    "timestamp": datetime.now().isoformat()
                }
            
            try:
                validation_result = excel_processor.validate_file(file_path)
                if validation_result["valid"]:
                    read_result = excel_processor.read_excel_file(file_path)
                    if read_result["success"]:
                        # 获取工作表详细信息
                        sheets_info = {}
                        for sheet_name, sheet_data in read_result["data"].items():
                            sheets_info[sheet_name] = {
                                "row_count": sheet_data["row_count"],
                                "column_count": sheet_data["column_count"],
                                "quality_score": sheet_data.get("quality_analysis", {}).get("quality_score", 0)
                            }
                        
                        return {
                            "code": 200,
                            "message": "Excel文件验证成功",
                            "data": {
                                "file_info": validation_result,
                                "data_info": {
                                    "total_sheets": len(read_result["data"]),
                                    "sheet_names": list(read_result["data"].keys()),
                                    "sheets_info": sheets_info,
                                    "total_rows": sum(sheet["row_count"] for sheet in read_result["data"].values()),
                                    "avg_quality_score": sum(sheet.get("quality_analysis", {}).get("quality_score", 0) for sheet in read_result["data"].values()) / len(read_result["data"])
                                }
                            },
                            "timestamp": datetime.now().isoformat()
                        }
                    else:
                        return {
                            "code": 400,
                            "message": read_result["message"],
                            "data": None,
                            "timestamp": datetime.now().isoformat()
                        }
                else:
                    return {
                        "code": 400,
                        "message": validation_result["message"],
                        "data": None,
                        "timestamp": datetime.now().isoformat()
                    }
            except Exception as e:
                logger.error(f"Excel文件测试失败: {str(e)}")
                return {
                    "code": 500,
                    "message": f"Excel文件处理失败: {str(e)}",
                    "data": None,
                    "timestamp": datetime.now().isoformat()
                }
        
        else:
            return {
                "code": 400,
                "message": f"不支持的数据源类型: {test_request.type}",
                "data": None,
                "timestamp": datetime.now().isoformat()
            }
        
    except Exception as e:
        logger.error(f"测试连接失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.post("/{data_source_id}/test")
@require_permissions(["data_source:test"])
@log_api_request("test_data_source")
async def test_data_source(
    data_source_id: int,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """测试指定数据源"""
    logger.info(f"测试数据源请求: {data_source_id}")
    
    try:
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限测试此数据源")
        
        # 测试连接
        result = data_source_manager.test_data_source_connection(data_source)
        
        # 记录历史
        data_source_manager.record_connection_history(
            db, 
            data_source_id, 
            "success" if result["success"] else "failed",
            result.get("message", ""),
            result
        )
        
        return result
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"测试数据源失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))




@data_sources_router.get("/{data_source_id}/stats")
@require_permissions(["data_source:read"])
@log_api_request("get_data_source_stats")
async def get_data_source_stats(
    data_source_id: int,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取数据源统计信息"""
    logger.info(f"获取数据源统计信息请求: {data_source_id}")
    
    try:
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限访问此数据源")
        
        # 获取统计信息
        stats = data_source_manager.get_data_source_stats(db, data_source_id)
        
        return stats
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取数据源统计信息失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.post("/upload-excel")
@require_permissions(["data_source:create"])
@log_api_request("upload_excel")
async def upload_excel_file(
    file: UploadFile = File(...),
    name: str = Form(...),
    description: str = Form(""),
    sheet_name: str = Form(None),
    has_header: bool = Form(True),
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """上传Excel文件创建数据源"""
    logger.info(f"上传Excel文件请求: {name}")
    
    try:
        # 验证文件类型
        if not file.filename.endswith(('.xlsx', '.xls', '.csv')):
            raise HTTPException(status_code=400, detail="不支持的文件格式，仅支持.xlsx, .xls, .csv")
        
        # 保存文件
        file_content = await file.read()
        file_path = excel_processor.save_uploaded_file(file_content, file.filename)
        
        # 验证文件
        validation_result = excel_processor.validate_file(file_path)
        if not validation_result["valid"]:
            # 删除文件
            excel_processor.delete_file(file_path)
            raise HTTPException(status_code=400, detail=validation_result["message"])
        
        # 创建数据源配置
        config = {
            "file_path": file_path,
            "file_name": file.filename,
            "sheet_name": sheet_name,
            "has_header": has_header
        }
        
        # 创建数据源
        data_source_dict = {
            "name": name,
            "description": description,
            "type": "excel",
            "config": config
        }
        
        new_data_source = data_source_manager.create_data_source(db, current_user.id, data_source_dict)
        
        # 返回标准格式的响应
        return {
            "code": 200,
            "message": "Excel文件上传成功",
            "data": {
                "id": new_data_source.id,
                "name": new_data_source.name,
                "type": new_data_source.source_type,
                "status": new_data_source.status,
                "config": new_data_source.config or {},
                "creator_id": new_data_source.creator_id,
                "created_at": new_data_source.created_at.isoformat(),
                "updated_at": new_data_source.updated_at.isoformat(),
                "file_info": validation_result
            }
        }
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"上传Excel文件失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.post("/database-query")
@require_permissions(["data_source:query"])
@log_api_request("database_query")
async def execute_database_query(
    query_request: DatabaseQueryRequest,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """执行数据库查询"""
    logger.info(f"执行数据库查询请求: 数据源ID {query_request.data_source_id}")
    
    try:
        # 获取数据源
        data_source = data_source_manager.get_data_source(db, query_request.data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限查询此数据源")
        
        # 检查数据源类型
        if data_source.source_type != "database":
            raise HTTPException(status_code=400, detail="只能查询数据库类型的数据源")
        
        # 获取连接并执行查询
        connection = data_source_manager.get_data_source_connection(data_source)
        if not connection:
            raise HTTPException(status_code=400, detail="无法获取数据库连接")
        
        result = connection.execute_query(query_request.query, query_request.limit)
        
        return result
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"执行数据库查询失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/{data_source_id}/tables")
@require_permissions(["data_source:read"])
@log_api_request("get_database_tables")
async def get_database_tables(
    data_source_id: int,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取数据库表列表"""
    logger.info(f"获取数据库表列表请求: {data_source_id}")
    
    try:
        # 获取数据源
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限访问此数据源")
        
        # 检查数据源类型
        if data_source.source_type != "database":
            raise HTTPException(status_code=400, detail="只能查询数据库类型的数据源")
        
        # 获取连接并获取表列表
        connection = data_source_manager.get_data_source_connection(data_source)
        if not connection:
            raise HTTPException(status_code=400, detail="无法获取数据库连接")
        
        tables = connection.get_table_list()
        
        return {"tables": tables}
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取数据库表列表失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/{data_source_id}/tables/{table_name}/schema")
@require_permissions(["data_source:read"])
@log_api_request("get_table_schema")
async def get_table_schema(
    data_source_id: int,
    table_name: str,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取表结构"""
    logger.info(f"获取表结构请求: {data_source_id}/{table_name}")
    
    try:
        # 获取数据源
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限访问此数据源")
        
        # 检查数据源类型
        if data_source.source_type != "database":
            raise HTTPException(status_code=400, detail="只能查询数据库类型的数据源")
        
        # 获取连接并获取表结构
        connection = data_source_manager.get_data_source_connection(data_source)
        if not connection:
            raise HTTPException(status_code=400, detail="无法获取数据库连接")
        
        schema = connection.get_table_schema(table_name)
        
        return schema
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取表结构失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/supported-types")
@require_permissions(["data_source:read"])
@log_api_request("get_supported_types")
async def get_supported_types():
    """获取支持的数据源类型"""
    logger.info("获取支持的数据源类型请求")
    
    try:
        return {
            "database_types": get_supported_database_types(),
            "data_source_types": data_source_manager.get_supported_data_source_types()
        }
        
    except Exception as e:
        logger.error(f"获取支持的数据源类型失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/excel-templates")
@require_permissions(["data_source:read"])
@log_api_request("get_excel_templates")
async def get_excel_templates():
    """获取Excel模板"""
    logger.info("获取Excel模板请求")
    
    try:
        templates = excel_template.get_all_templates()
        return {"templates": templates}
        
    except Exception as e:
        logger.error(f"获取Excel模板失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.post("/health-check")
@require_permissions(["data_source:admin"])
@log_api_request("health_check")
async def health_check_data_sources(
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """健康检查所有数据源"""
    logger.info("数据源健康检查请求")
    
    try:
        # 权限检查
        if not current_user.is_superuser:
            raise HTTPException(status_code=403, detail="只有管理员可以执行健康检查")
        
        # 执行健康检查
        results = data_source_manager.health_check_all_data_sources(db)
        
        return results
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"数据源健康检查失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/{data_source_id}/schema", response_model=List[TableSchemaResponse])
@require_permissions(["data_source:read"])
@log_api_request("get_data_source_schema")
async def get_data_source_schema(
    data_source_id: int,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取数据源结构信息"""
    logger.info(f"获取数据源结构信息请求: {data_source_id}")
    
    try:
        # 获取数据源
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限访问此数据源")
        
        # 检查预览功能是否启用
        if not data_source.preview_enabled:
            raise HTTPException(status_code=400, detail="此数据源未启用预览功能")
        
        # 获取结构信息
        schema_info = data_source_manager.get_data_source_schema(db, data_source_id)
        
        return schema_info
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取数据源结构信息失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.post("/test-excel-connection")
@require_permissions(["data_source:test"])
@log_api_request("test_excel_connection")
async def test_excel_connection(
    file: UploadFile = File(...),
    sheet_name: str = Form(None),
    has_header: bool = Form(True),
    current_user: User = Depends(get_current_user)
):
    """测试Excel文件连接"""
    logger.info("测试Excel文件连接请求")
    
    try:
        # 验证文件类型
        if not file.filename.endswith(('.xlsx', '.xls')):
            return {"success": False, "message": "只支持Excel文件 (.xlsx, .xls)"}
        
        # 创建临时文件
        with tempfile.NamedTemporaryFile(delete=False, suffix='.xlsx') as tmp_file:
            # 保存上传的文件
            content = await file.read()
            tmp_file.write(content)
            tmp_file_path = tmp_file.name
        
        try:
            # 验证Excel文件
            validation_result = excel_processor.validate_file(tmp_file_path)
            if not validation_result["valid"]:
                return {"success": False, "message": validation_result["message"]}
            
            # 读取Excel文件
            read_result = excel_processor.read_excel_file(tmp_file_path, sheet_name=sheet_name)
            if not read_result["success"]:
                return {"success": False, "message": read_result["message"]}
            
            # 获取工作表详细信息
            sheets_info = {}
            target_sheet_data = None
            
            for sheet_name_key, sheet_data in read_result["data"].items():
                sheet_info = {
                    "row_count": sheet_data["row_count"],
                    "column_count": sheet_data["column_count"],
                    "quality_score": sheet_data.get("quality_analysis", {}).get("quality_score", 0)
                }
                sheets_info[sheet_name_key] = sheet_info
                
                # 如果指定了工作表名称，找到目标工作表数据
                if sheet_name and sheet_name_key == sheet_name:
                    target_sheet_data = sheet_data
                elif not sheet_name and target_sheet_data is None:
                    # 如果没有指定工作表，使用第一个工作表
                    target_sheet_data = sheet_data
            
            # 如果没有指定工作表，使用第一个工作表
            if not target_sheet_data and read_result["data"]:
                first_sheet_name = list(read_result["data"].keys())[0]
                target_sheet_data = read_result["data"][first_sheet_name]
            
            # 清理numpy类型，确保可以JSON序列化
            def clean_numpy_types(obj):
                import numpy as np
                if isinstance(obj, dict):
                    return {k: clean_numpy_types(v) for k, v in obj.items()}
                elif isinstance(obj, list):
                    return [clean_numpy_types(item) for item in obj]
                elif isinstance(obj, (np.integer, np.int64)):
                    return int(obj)
                elif isinstance(obj, (np.floating, np.float64)):
                    return float(obj)
                elif isinstance(obj, np.bool_):
                    return bool(obj)
                elif isinstance(obj, np.ndarray):
                    return obj.tolist()
                else:
                    return obj
            
            cleaned_sheets_info = clean_numpy_types(sheets_info)
            cleaned_current_sheet = None
            if target_sheet_data:
                cleaned_current_sheet = {
                    "row_count": int(target_sheet_data["row_count"]) if target_sheet_data["row_count"] is not None else 0,
                    "column_count": int(target_sheet_data["column_count"]) if target_sheet_data["column_count"] is not None else 0,
                    "columns": target_sheet_data["columns"][:10] if target_sheet_data["columns"] else []  # 只返回前10列
                }
            
            return {
                "success": True,
                "message": "Excel文件验证成功",
                "file_info": clean_numpy_types(validation_result),
                "data_info": {
                    "total_sheets": len(read_result["data"]),
                    "sheet_names": list(read_result["data"].keys()),
                    "sheets": cleaned_sheets_info,
                    "current_sheet": cleaned_current_sheet
                }
            }
            
        finally:
            # 清理临时文件
            if os.path.exists(tmp_file_path):
                os.unlink(tmp_file_path)
                
    except Exception as e:
        logger.error(f"Excel文件测试失败: {str(e)}")
        return {"success": False, "message": f"Excel文件处理失败: {str(e)}"}


@data_sources_router.post("/preview-excel")
@require_permissions(["data_source:read"])
@log_api_request("preview_excel_data")
async def preview_excel_data(
    file: UploadFile = File(...),
    sheet_name: str = Form(None),
    has_header: bool = Form(True),
    limit: int = Form(100),
    offset: int = Form(0),
    current_user: User = Depends(get_current_user)
):
    """预览Excel文件数据"""
    logger.info(f"预览Excel文件数据请求: {file.filename}")
    
    try:
        # 验证文件类型
        if not file.filename.endswith(('.xlsx', '.xls', '.csv')):
            raise HTTPException(status_code=400, detail="不支持的文件格式，仅支持.xlsx, .xls, .csv")
        
        # 读取文件内容
        file_content = await file.read()
        
        # 保存临时文件
        with tempfile.NamedTemporaryFile(delete=False, suffix=Path(file.filename).suffix) as tmp_file:
            tmp_file.write(file_content)
            temp_file_path = tmp_file.name
        
        try:
            # 验证和读取文件
            validation_result = excel_processor.validate_file(temp_file_path)
            if not validation_result["valid"]:
                raise HTTPException(status_code=400, detail=validation_result["message"])
            
            # 读取Excel文件
            read_result = excel_processor.read_excel_file(temp_file_path, sheet_name)
            if not read_result["success"]:
                raise HTTPException(status_code=400, detail=read_result["message"])
            
            # 获取指定工作表的数据
            sheets_data = read_result["data"]
            logger.info(f"可用工作表: {list(sheets_data.keys())}")
            
            target_sheet_name = sheet_name or list(sheets_data.keys())[0]
            logger.info(f"目标工作表: {target_sheet_name}")
            
            if target_sheet_name not in sheets_data:
                raise HTTPException(status_code=400, detail=f"工作表 '{target_sheet_name}' 不存在")
            
            sheet_data = sheets_data[target_sheet_name]
            logger.info(f"工作表数据: {sheet_data}")
            
            # 检查必要的数据字段
            if "columns" not in sheet_data:
                raise HTTPException(status_code=500, detail="工作表数据缺少columns字段")
            
            if "preview_data" not in sheet_data:
                raise HTTPException(status_code=500, detail="工作表数据缺少preview_data字段")
            
            # 处理标题行
            columns = sheet_data["columns"]
            preview_data = sheet_data["preview_data"]
            
            logger.info(f"列数: {len(columns)}, 预览数据行数: {len(preview_data) if preview_data else 0}")
            
            if has_header:
                # 使用第一行作为列名
                header_row = preview_data[0] if preview_data and len(preview_data) > 0 else []
                logger.info(f"标题行: {header_row}")
                
                columns = [
                    {
                        "name": str(header_row[i]) if i < len(header_row) else f"列{i+1}",
                        "type": col.get("type", "string"),
                        "null_count": col.get("null_count", 0),
                        "unique_count": col.get("unique_count", 0)
                    }
                    for i, col in enumerate(columns)
                ]
                # 数据从第二行开始
                data_rows = preview_data[1:] if preview_data and len(preview_data) > 1 else []
            else:
                # 生成默认列名
                columns = [
                    {
                        "name": f"列{i+1}",
                        "type": col.get("type", "string"),
                        "null_count": col.get("null_count", 0),
                        "unique_count": col.get("unique_count", 0)
                    }
                    for i, col in enumerate(columns)
                ]
                # 数据从第一行开始
                data_rows = preview_data if preview_data else []
            
            logger.info(f"数据行数: {len(data_rows)}")
            
            # 应用分页
            if not data_rows:
                logger.warning("没有数据行可以分页")
                paginated_data = []
            else:
                start_idx = min(offset, len(data_rows))
                end_idx = min(offset + limit, len(data_rows))
                paginated_data = data_rows[start_idx:end_idx]
                logger.info(f"分页范围: {start_idx} 到 {end_idx}, 实际数据行数: {len(paginated_data)}")
            
            # 转换数据格式
            data = []
            for row_idx, row in enumerate(paginated_data):
                row_dict = {}
                for i, col in enumerate(columns):
                    try:
                        row_dict[col["name"]] = row[i] if i < len(row) else ""
                    except IndexError:
                        logger.warning(f"行 {row_idx} 列 {i} 索引超出范围")
                        row_dict[col["name"]] = ""
                    except Exception as e:
                        logger.error(f"处理行 {row_idx} 列 {i} 时出错: {e}")
                        row_dict[col["name"]] = ""
                data.append(row_dict)
            
            return DataPreviewResponse(
                table_name=target_sheet_name,
                columns=columns,
                data=data,
                total_records=len(data_rows),
                returned_records=len(data),
                offset=offset,
                limit=limit,
                execution_time=0  # 前端处理，执行时间为0
            )
            
        finally:
            # 清理临时文件
            try:
                os.unlink(temp_file_path)
            except:
                pass
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"预览Excel数据失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.post("/{data_source_id}/preview", response_model=DataPreviewResponse)
@require_permissions(["data_source:read"])
@log_api_request("preview_data_source")
async def preview_data_source(
    data_source_id: int,
    preview_request: DataPreviewRequest,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """预览数据源数据"""
    logger.info(f"预览数据源数据请求: {data_source_id}")
    
    try:
        # 获取数据源
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限访问此数据源")
        
        # 检查预览功能是否启用
        if not data_source.preview_enabled:
            raise HTTPException(status_code=400, detail="此数据源未启用预览功能")
        
        # 限制预览行数
        limit = min(preview_request.limit, data_source.preview_limit)
        
        # 预览数据
        preview_result = data_source_manager.preview_data_source(
            db, 
            data_source_id, 
            table_name=preview_request.table_name,
            sheet_name=preview_request.sheet_name,
            limit=limit,
            offset=preview_request.offset
        )
        
        return preview_result
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"预览数据源数据失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.get("/{data_source_id}/preview-history")
@require_permissions(["data_source:read"])
@log_api_request("get_preview_history")
async def get_preview_history(
    data_source_id: int,
    page: int = 1,
    page_size: int = 20,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取数据源预览历史"""
    logger.info(f"获取数据源预览历史请求: {data_source_id}")
    
    try:
        # 获取数据源
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限访问此数据源")
        
        # 获取预览历史
        history = data_source_manager.get_preview_history(
            db, 
            data_source_id, 
            page=page, 
            page_size=page_size
        )
        
        return history
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取数据源预览历史失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@data_sources_router.put("/{data_source_id}/preview-config")
@require_permissions(["data_source:update"])
@log_api_request("update_preview_config")
async def update_preview_config(
    data_source_id: int,
    preview_config: Dict[str, Any],
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """更新数据源预览配置"""
    logger.info(f"更新数据源预览配置请求: {data_source_id}")
    
    try:
        # 获取数据源
        data_source = data_source_manager.get_data_source(db, data_source_id)
        if not data_source:
            raise HTTPException(status_code=404, detail="数据源不存在")
        
        # 权限检查
        if not current_user.is_superuser and data_source.creator_id != current_user.id:
            raise HTTPException(status_code=403, detail="无权限修改此数据源")
        
        # 更新预览配置
        updated_data_source = data_source_manager.update_preview_config(
            db, 
            data_source_id, 
            preview_config
        )
        
        return {"message": "预览配置更新成功", "data_source_id": data_source_id}
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"更新数据源预览配置失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))